/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.myapp.struts;

import java.sql.*;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
//import org.hibernate.Hibernate;
//import org.hibernate.Query;
//import org.hibernate.Session;
//import org.hibernate.SessionFactory;

/**
 *
 * @author Roberto
 */
public class UsuarioHelper {

   // Session session = null;
    Connection conexion = null;

   
    public UsuarioHelper() {
        
        try{
              Class.forName("org.postgresql.Driver");
              this.conexion=DriverManager.getConnection(
					"jdbc:postgresql://localhost:5432/postgres","postgres",
		"lexmark1388");
       }
       catch (Exception e){
           System.err.println("Usuario no conectado");
           //e.printStackTrace();
           System.err.println(e.getCause());
       }
       
         
        //this.session = HibernateUtil.getSessionFactory().getCurrentSession();
    }
    public boolean isConnected(){
        if(this.getConexion()==null){
            return false;
        }
        return true;
     }
    //condicionales para verificar conexion
    public List getUsers() throws SQLException {
        List<Usuario> userList = null;
                           
        try {
            //org.hibernate.Transaction tx = session.beginTransaction();
            Statement statement= this.getConexion().createStatement();          
            ResultSet query=statement.executeQuery("Select * from postgres.usuario");
            System.out.println("query");
            while(query.next()){
                System.out.println(query.getString("usuario"));
                Usuario u = new Usuario(query.getString("usuario"),query.getString("password"),query.getString("nombre"),query.getString("correo"),query.getString("categoria"));
                userList.add(u);
            }
            statement.close();
            
        } catch (Exception e) {
            System.err.println(e.getMessage());
            
        } 
       
        return userList;
    }
    
    // Obtener la lista de usuarios
    public List getTablas() {
        Connection c= this.getConexion();
        List userList = new LinkedList();
       try {
         
            //org.hibernate.Transaction tx = session.beginTransaction();
            Statement statement= c.createStatement();          
            ResultSet query=statement.executeQuery("Select a.nombre from Tablas a");
            while(query.next()){
                Usuario u = new Usuario(query.getString("usuario"),query.getString("password"),query.getString("nombre"),query.getString("correo"),query.getString("categoria"));
                userList.add(u);
            }
            statement.close();
            
        } catch (Exception e) {
            System.err.println(e.getMessage());
        } 
        return userList;
    }
    
    /*
      public Fecha getFecha() {
        Fecha fecha = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("from Fecha");
            fecha = (Fecha) q.list();
        } catch (Exception e) {
        }
        return fecha;
    }
*/
    
    public boolean existeUsuario(String username) {
        boolean existe = false;
        Connection c= this.getConexion();
        try {
//            org.hibernate.Transaction tx = session.beginTransaction();
            Statement statement= c.createStatement();
            ResultSet query=statement.executeQuery("select usuario from Usuario where u.usuario= '"+username+"'");
            //Query q = session.createQuery("FROM Usuario u WHERE u.usuario = '" + username + "'");
            while(query.next()&& existe==true){
                if(query.getString("usuario").equalsIgnoreCase(username)){
                    existe = true;
                }
            }
         /*   if (!q.list().isEmpty()) {
                existe = true;
            }*/
            statement.close();
        } catch (Exception e) {System.err.println(e.getMessage());
        
        }
        return existe;
    }
     public List getUsersOrder(String tipo) {
        List<Usuario> userList = null;
        Connection c= this.getConexion();
        try {
         
            //org.hibernate.Transaction tx = session.beginTransaction();
            Statement statement= c.createStatement();          
            ResultSet query=statement.executeQuery("Select * from usuario order by u."+ tipo + " ASC");
            while(query.next()){
                Usuario u = new Usuario(query.getString("usuario"),query.getString("password"),query.getString("nombre"),query.getString("correo"),query.getString("categoria"));
                userList.add(u);
            }
            c.close();
        } catch (Exception e) {
            System.err.println(e.getMessage());
        } 
        
        return userList;
    }
   
    public boolean loginAccess(String id, String password, List userList) {
        
        System.out.println(id + " " + password);
        if(userList!=null){
            Iterator i = userList.iterator();
            while (i.hasNext()) {
                Usuario u = (Usuario) i.next();
                if (u.getUsuario().equals(id)) {
                    if (u.getPassword().equals(password)) {
                        return true;
                    }
                }
            }
        }
        
        return false;
    }
    
    public Usuario infoUser(String id) throws SQLException {
        Connection c= this.getConexion();
        List<Usuario> userList = getUsers();
        Iterator i = userList.iterator();
        while (i.hasNext()) {
            Usuario u = (Usuario) i.next();
            if (u.getUsuario().equals(id)) {
                return u;
            }
        }
        return null;
    }
    
    public void insertUser(Usuario u) {
        Connection c= this.getConexion();
        
        try{
        Statement statement= c.createStatement();
        statement.executeUpdate("insert into usuario"
                + "(usuario,password,nombre,correo,categoria) "
                + "values('"+u.getUsuario()+"','"+u.getPassword()+"','"+u.getNombre()+"','"+u.getCorreo()+"','"+u.getCategoria()+"')");
        statement.close();
         c.close();
        }
        
       
        catch(Exception e){}
        
    }
    
    public void deleteUser(String id) {
        try {
            Connection c= this.getConexion();
            Statement statement=c.createStatement();
            statement.executeUpdate("delete from usuario where usuario= '"+id+"'");
           /* Usuario user = (Usuario) session.load(Usuario.class,id);
            session.delete(user);
            session.getTransaction().commit();*/
            statement.close();
        } catch (Exception e) {System.out.println(e);
        }
    }
    
	public void modUser(Usuario newuser) {
            Connection c= this.getConexion();
        /*try {
            this.session.beginTransaction();
            Usuario user = (Usuario) session.get(Usuario.class, newuser.getUsuario());
            user.setCorreo(newuser.getCorreo());
            user.setNombre(newuser.getNombre());
            user.setPassword(newuser.getPassword());
            user.setUsuario(newuser.getUsuario());
            user.setCategoria(newuser.getCategoria());
            //session.saveOrUpdate(newuser);
            session.getTransaction().commit();
        } catch (Exception e) {System.out.println(e);
        }*/
    }
	
    public List categoriaUsuarios(String categoria, List userList) {
        List<Usuario> lista = new LinkedList<Usuario>();
        Iterator i = userList.iterator();
        while (i.hasNext()) {
            Usuario u = (Usuario) i.next();
            if (u.getCategoria() != null && u.getCategoria().equals(categoria)) {
                //lista.add(u);
                System.out.println("Usuario: " + u.getUsuario());
                System.out.println("Correo: " + u.getCorreo());
                System.out.println("Nombre: " + u.getNombre());
                lista.add(u);
            }
        }
        return lista;
    }

    public void crearTabla(String nametable, List<String> parametros) {
        Connection c= this.getConexion();
            try {
                    String q = "CREATE TABLE " + nametable + " (";
        Iterator i = parametros.iterator();
        int k = 0;
        while (i.hasNext()) {
            String p = (String) i.next();
            if (k != 0) {
                    q = q + ", " + p + " VARCHAR (30)";
            }else {
                q = q + p + " VARCHAR (30)";
                k++;
            }
        }
        q = q + ")";
        Statement s= c.createStatement();
        s.execute(q);
        c.close();
      } catch (Exception e) {System.out.println(e);}
    }

    public void eliminarTabla(String nametable) {
        Connection c= this.getConexion();
    try {
         String q = "DROP TABLE " + nametable;
        Statement s= c.createStatement();
        s.execute(q);
       c.close();
    } catch (Exception e) {System.out.println(e);}
    
    }
    
    
    public void eliminarAtributo(String nametable,String atributo) {
    try {
         Connection c= this.getConexion();
         String q = "ALTER TABLE " + nametable +" DROP COLUMN " +atributo;
          Statement s= c.createStatement();
            s.execute(q);
            c.close();
    } catch (Exception e) {System.out.println(e);}
    }
    
    public void agregarAtributo(String nametable,String atributo) {
    try {
        Connection c= this.getConexion();
         String q = "ALTER TABLE " + nametable +" ADD COLUMN " +atributo+" character varying(30)";
          Statement s= c.createStatement();
        s.execute(q);
        c.close();
    } catch (Exception e) {System.out.println(e);}
    }
    
    public void disconnect(){
        try{
             this.getConexion().close();
             this.conexion=null;
        }
        catch(Exception e){
            System.err.println(e.getMessage());
        }
           
   
    
    }
    public void insertTabla(String nombre, String categoria) {
        Connection c= this.getConexion();
        //this.session.beginTransaction();
        Tablas tabla = new Tablas();
        tabla.setNombre(nombre);
        tabla.setCategoria(categoria);
        try{
        c.close();
        }
        catch(Exception e){}
        //session.save(tabla);
        //session.getTransaction().commit();
    }
    public List getAtributos(String tabla) {
      /*  List userList = new LinkedList();
        try {
          
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery(" \\dt tabla");
            userList = (List) q.list();

        } catch (Exception e) {
        }
        return userList; */
        return new LinkedList();
    }
     public Connection getConexion() {
        return conexion;
    }

    public void setConexion(Connection conexion) {
        
        this.conexion = conexion;
    }
  //  \dt tabla
}
